using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;

using PickGold.Database;
using PickGold.Entities.Table;

namespace PickGold.Util
{
    public class CQueries
    {
        //
        public static int QUERY_SETTING = 1;
        public static int QUERY_PATH = 2;
        public static int QUERY_MIME = 3;
        //
        protected IKernel _Kernel;
        protected string _tQueryKey = null;
        protected string _tValue = null;
        //
        public void setKernel(IKernel value)
        {
            this._Kernel = value;
			//if(value == null)
			//	base.data
        }
        //
        public CQueries(IKernel kernel)
        {
			this.setKernel(kernel);
        }
        //
        public string QueryKey
        {
            get
            {
                if (this._tQueryKey == null)
                    this._tQueryKey = this._Kernel.Settings["key/query"];
                return this._tQueryKey;
            }
        }
        //
        public string this[int index]
        {
            set
            {
                this.setQuery(value, index);
            }
            get
            {
                return this.getQuery(index);
            }
        }
        //
        public string this[string name]
        {
            set
            {
                this.setQuery(value, name);
            }
            get
            {
                return this.getQuery(name);
            }
        }
        //
        public string getQuery()
        {
            return this._tValue;
        }
        public string getQuery(int index)
        {
            if (index < 0) return getQuery();
            //IDataCommand cmd = new IDataCommand(this._Kernel.Database);
            //CBaseQueryInfo entity = new CBaseQueryInfo(this._Kernel.Database);
            //cmd.AddField(entity.GetColumn(CBaseQueryInfo.ITEM_REVIEW), 1, EOperator.Self);
            //cmd.AddField(entity.GetColumn(CBaseQueryInfo.ITEM_TIME), DateTime.Now);
            //cmd.AddField(entity.GetColumn(CBaseQueryInfo.ITEM_ID), 
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "Update [tb_sys_query_info] Set [item_review] = [item_review] + 1, [item_time] = GetDate()\r\n" +
                        "Where [item_id] = @item_id And ([user_id] = @user_id Or [user_id] < 0);";
            cmd.Parameters.AddWithValue("item_id", index);
            cmd.Parameters.AddWithValue("user_id", this._Kernel.Userinfo.ID);
            if (this._Kernel.Database.Execute(cmd) == 0) return null;
            cmd.CommandText = "Select [item_value] From [tb_sys_query_info] Where [item_id] = @item_id And ([user_id] = @user_id Or [user_id] < 0);";
            this._tValue = (string)cmd.ExecuteScalar();
            return this._tValue;
        }
        public string getQuery(string name)
        {
            if (string.IsNullOrEmpty(name))
                return getQuery();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "Update [tb_sys_query_info] Set [item_review] = [item_review] + 1, [item_time] = GetDate()\r\n" +
                        "Where [item_name] = @item_name And ([user_id] = @user_id Or [user_id] < 0);";
            cmd.Parameters.AddWithValue("item_name", name);
            cmd.Parameters.AddWithValue("user_id", this._Kernel.Userinfo.ID);
            if (this._Kernel.Database.Execute(cmd) == 0) return null;
            cmd.CommandText = "Select [item_value] From [tb_sys_query_info] Where [item_name] = @item_name And ([user_id] = @user_id Or [user_id] < 0);";
            this._tValue = (string)cmd.ExecuteScalar();
            return this._tValue;
        }
        //
        public int setQuery(string value)
        {
            return this.setQuery(value, null, -1, null, -1, false);
        }
        public int setQuery(string value, int index)
        {
            return this.setQuery(value, null, index, null, -1, false);
        }
        public int setQuery(string value, string name)
        {
            if (name == null || name == "")
                return this.setQuery(value, name, -1, null, -1, false);
            else
                return this.setQuery(value, name, -1, null, -1, true);
        }
        public int setQuery(string value, string name, int index)
        {
            if (name == null || name == "")
                return this.setQuery(value, name, index, null, -1, false);
            else
                return this.setQuery(value, name, index, null, -1, true);
        }
        public int setQuery(string value, string name, int index, string path, int up)
        {
            if (name == null || name == "")
                return this.setQuery(value, name, index, path, up, false);
            else
                return this.setQuery(value, name, index, path, up, true);
        }
        public int setQuery(string value, string name, int index, string path, int up, bool save)
        {
            int i = 0;
            if (value == null || value == "") return -1;
            SqlCommand cmd = new SqlCommand();
            if (index < 0)
            {
                cmd.CommandText = "Select IsNull(Min([item_id]), -1) From [tb_sys_query_info]\r\n" +
                        "Where [item_flags] = @flags Or (DateDiff(Day, GetDate(), [item_time]) <> 0 And [query_saved] = 0)" +
                        "Or (DateDiff(Month, GetDate(), [item_time]) <> 0 And [user_id] >= 0);";
                cmd.Parameters.AddWithValue("flags", CFlags.DELETED);
                index = (int)this._Kernel.Database.Scalar(cmd);
            }
            if (i < 0)
            {
                cmd.CommandText = "Select IsNull(Max([item_id]), -1) + 1 From [tb_sys_query_info];";
                index = (int)cmd.ExecuteScalar();
            }
            if (up < 0) up = 0;
            if (string.IsNullOrEmpty(name))
                name = this.QueryKey + "_" + up + "_" + index;
            if (string.IsNullOrEmpty(path))
                path = "";// path = this._Kernel.Request.ServerVariables["SCRIPT_NAME"];
            cmd.Parameters.Clear();
            cmd.CommandText = "Select Count(*) From [tb_sys_query_info] Where [item_id] = @item_id;";
            cmd.Parameters.AddWithValue("item_id", index);
            i = (int)cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            if (i == 0)
            {
                cmd.CommandText = "Insert Into\r\n" +
                        "[tb_sys_query_info]([item_id], [item_name], [super_id], [user_id], [item_path], [item_value])\r\n" +
                        "Value(@item_id, @item_name, @super_id, @user_id, @item_path, @item_value);";
            }
            else
            {
                cmd.CommandText = "Update [tb_sys_query_info] Set\r\n" +
                        "[item_name] = @query_name,\r\n" +
                        "[super_id] = @query_up,\r\n" +
                        "[user_id] = @user_id,\r\n" +
                        "[item_path] = @query_path,\r\n" +
                        "[item_value] = @query_value,\r\n" +
                        "[item_time] = GetDate()\r\n" +
                        "[item_flags] = 0,\r\n" +
                        "Where [item_id] = @item_id;";
            }
            cmd.Parameters.AddWithValue("item_id", index);
            cmd.Parameters.AddWithValue("item_name", name);
            cmd.Parameters.AddWithValue("super_id", up);
            cmd.Parameters.AddWithValue("user_id", this._Kernel.Userinfo.ID);
            cmd.Parameters.AddWithValue("item_path", path);
            cmd.Parameters.AddWithValue("item_value", value);
            cmd.Parameters.AddWithValue("item_flags", save);
            cmd.ExecuteNonQuery();
            this._tValue = value;
            return index;
        }
        //
        public int delQuery(int index)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "Update [t_sys_query_info] Set [item_time] = GetDate(), [property] = 255\r\n" +
                        "Where [query_id] = @query_id And [user_id] = @user_id;";
            cmd.Parameters.AddWithValue("query_id", index);
            cmd.Parameters.AddWithValue("user_id", this._Kernel.Userinfo.ID);
            return this._Kernel.Database.Execute(cmd);
        }
        public int delQuery(string name)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "Update [t_sys_query_info] Set [item_time] = GetDate(), [property] = 255\r\n" +
                        "Where [query_name] = @query_name And [user_id] = @user_id;";
            cmd.Parameters.AddWithValue("query_name", name);
            cmd.Parameters.AddWithValue("user_id", this._Kernel.Userinfo.ID);
            return this._Kernel.Database.Execute(cmd);
        }
        //
    }
}
